
alter proc rpt_AI_FinancialReport
@date datetime
as
begin
	declare @firstDate as datetime
	set @firstDate = DATEADD(mm, DATEDIFF(mm, 0, @date), 0)
	declare @lastDate as datetime
	set @lastDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date)+1,0))
	
	declare @firstLM as datetime
	set @firstLM = DATEADD(MM, -1, @firstDate)
	declare @lastLM as datetime
	set @lastLM = DATEADD(MM, -1, @lastDate)
	
	declare @firstDateYear as datetime
	set @firstDateYear = DATEADD(yy, DATEDIFF(yy,0,@date), 0)

	declare @fistDateLY as datetime
	set @fistDateLY = DATEADD(yy, -1, @firstDateYear)
	declare @dateLY as datetime
	set @dateLY = DATEADD(yy, -1, @date)

	declare @tab table(
		MOrder int,
		COA nvarchar(100),
		PrcCode nvarchar(100),
		PrcName nvarchar(100),
		curAmt numeric(19, 2),
		prevAmt numeric(19, 2),
		ytdActualAmt numeric(19, 2),
		ytdLYAmt numeric(19, 2),
		grMask int
	)
	insert into @tab 
	select 1 MOrder, 'REVENUE' COA, isnull(pr.PrcCode,'#NA#') PrcCode, isnull(pr.PrcName,'Not specific') PrcName,
	SUM(T.curAmt) curAmt, SUM(T.prevAmt) prevAmt, SUM(T.ytdActualAmt) ytdActualAmt, SUM(T.ytdLYAmt) ytdLYAmt, 4
	from 
	(
		select jd.Account, jd.ProfitCode, 
			jd.Credit-jd.Debit curAmt, 0 prevAmt, 0 ytdActualAmt, 0 ytdLYAmt 
			from JDT1 jd		
			where jd.RefDate between @firstDate and @lastDate 		
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt, jd.Credit-jd.Debit prevAmt, 0 ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstLM and @lastLM
		union all 		
		select jd.Account, jd.ProfitCode, 
			0 curAmt, 0 prevAmt, jd.Credit-jd.Debit ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstDateYear and @date
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt,  0 prevAmt, 0 ytdActualAmt, jd.Credit-jd.Debit ytdLYAmt
			from JDT1 jd
			where jd.RefDate between @fistDateLY and @dateLY
	) T 
	left join OPRC pr on T.ProfitCode = pr.PrcCode
	left join OACT ca on T.Account = ca.AcctCode
	where ca.GroupMask = 4	
	group by  pr.PrcCode, pr.PrcName
	
	UNION ALL
	
	select 2 MOrder, 'COST OF SALE' COA, isnull(pr.PrcCode,'#NA#') PrcCode, isnull(pr.PrcName,'Not specific') PrcName, 
	case when SUM(T.curAmt)<0 then -SUM(T.curAmt) else SUM(T.curAmt) end curAmt,
	CASE when SUM(T.prevAmt)<0 then -SUM(T.prevAmt) else SUM(T.prevAmt)end  prevAmt, 
	case when SUM(T.ytdActualAmt)<0 then -SUM(T.ytdActualAmt) else SUM(T.ytdActualAmt) end ytdActualAmt, 
	case when SUM(T.ytdLYAmt)<0 then -SUM(T.ytdLYAmt) else SUM(T.ytdLYAmt) end ytdLYAmt, 5
	from 
	(
		select jd.Account, jd.ProfitCode, 
			jd.Credit-jd.Debit curAmt, 0 prevAmt, 0 ytdActualAmt, 0 ytdLYAmt 
			from JDT1 jd		
			where jd.RefDate between @firstDate and @lastDate 		
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt, jd.Credit-jd.Debit prevAmt, 0 ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstLM and @lastLM
		union all 		
		select jd.Account, jd.ProfitCode, 
			0 curAmt, 0 prevAmt, jd.Credit-jd.Debit ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstDateYear and @date
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt,  0 prevAmt, 0 ytdActualAmt, jd.Credit-jd.Debit ytdLYAmt
			from JDT1 jd
			where jd.RefDate between @fistDateLY and @dateLY

	) T 
	left join OPRC pr on T.ProfitCode = pr.PrcCode
	left join OACT ca on T.Account = ca.AcctCode
	
	where ca.GroupMask = 5
	
	group by pr.PrcCode, pr.PrcName
	
	UNION ALL
	
	select 3 MOrder, 'OPERATING COSTS' COA, isnull(pr.PrcCode,'#NA#') PrcCode, isnull(pr.PrcName,'Not specific') PrcName, 
	case when SUM(T.curAmt)<0 then -SUM(T.curAmt) else SUM(T.curAmt) end curAmt,
	CASE when SUM(T.prevAmt)<0 then -SUM(T.prevAmt) else SUM(T.prevAmt)end  prevAmt, 
	case when SUM(T.ytdActualAmt)<0 then -SUM(T.ytdActualAmt) else SUM(T.ytdActualAmt) end ytdActualAmt, 
	case when SUM(T.ytdLYAmt)<0 then -SUM(T.ytdLYAmt) else SUM(T.ytdLYAmt) end ytdLYAmt, 6
	from 
	(
		select jd.Account, jd.ProfitCode, 
			jd.Credit-jd.Debit curAmt, 0 prevAmt, 0 ytdActualAmt, 0 ytdLYAmt 
			from JDT1 jd		
			where jd.RefDate between @firstDate and @lastDate 		
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt, jd.Credit-jd.Debit prevAmt, 0 ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstLM and @lastLM
		union all 		
		select jd.Account, jd.ProfitCode, 
			0 curAmt, 0 prevAmt, jd.Credit-jd.Debit ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstDateYear and @date
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt,  0 prevAmt, 0 ytdActualAmt, jd.Credit-jd.Debit ytdLYAmt
			from JDT1 jd
			where jd.RefDate between @fistDateLY and @dateLY

	) T 
	left join OPRC pr on T.ProfitCode = pr.PrcCode
	left join OACT ca on T.Account = ca.AcctCode
	
	where ca.GroupMask =6 
	group by pr.PrcCode, pr.PrcName
	
	UNION ALL
	
	select 4 MOrder, 'NON OPERATING COSTS' COA, isnull(pr.PrcCode,'#NA#') PrcCode, isnull(pr.PrcName,'Not specific') PrcName, 
	case when SUM(T.curAmt)<0 then -SUM(T.curAmt) else SUM(T.curAmt) end curAmt,
	CASE when SUM(T.prevAmt)<0 then -SUM(T.prevAmt) else SUM(T.prevAmt)end  prevAmt, 
	case when SUM(T.ytdActualAmt)<0 then -SUM(T.ytdActualAmt) else SUM(T.ytdActualAmt) end ytdActualAmt, 
	case when SUM(T.ytdLYAmt)<0 then -SUM(T.ytdLYAmt) else SUM(T.ytdLYAmt) end ytdLYAmt, 7
	from 
	(
		select jd.Account, jd.ProfitCode, 
			jd.Credit-jd.Debit curAmt, 0 prevAmt, 0 ytdActualAmt, 0 ytdLYAmt 
			from JDT1 jd		
			where jd.RefDate between @firstDate and @lastDate 		
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt, jd.Credit-jd.Debit prevAmt, 0 ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstLM and @lastLM
		union all 		
		select jd.Account, jd.ProfitCode, 
			0 curAmt, 0 prevAmt, jd.Credit-jd.Debit ytdActualAmt, 0 ytdLYAmt
			from JDT1 jd			
			where jd.RefDate between @firstDateYear and @date
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt,  0 prevAmt, 0 ytdActualAmt, jd.Credit-jd.Debit ytdLYAmt
			from JDT1 jd
			where jd.RefDate between @fistDateLY and @dateLY

	) T 
	left join OPRC pr on T.ProfitCode = pr.PrcCode
	left join OACT ca on T.Account = ca.AcctCode
	
	where ca.GroupMask = 7 
	group by pr.PrcCode, pr.PrcName
	
	order by MOrder
	
	insert into @tab
	select 5, 'NET PROFIT', t4.PrcCode, t4.PrcName, isnull(t4.curAmt, 0) - isnull(t5.curAmt, 0), isnull(t4.prevAmt, 0) - isnull(t5.prevAmt, 0), isnull(t4.ytdActualAmt, 0) - isnull(t5.ytdActualAmt, 0),
	t4.ytdLYAmt - t5.ytdLYAmt, 10
	 from @tab t4 join @tab t5 on t4.PrcCode = t5.PrcCode and t4.grMask = 4 and t5.grMask = 5
	
	insert into @tab
	select 6, 'GROSS PROFIT', t4.PrcCode, t4.PrcName, 
	isnull(t4.curAmt, 0) - isnull(t5.curAmt, 0) - isnull(t6.curAmt, 0) - isnull(t7.curAmt, 0), 
	isnull(t4.prevAmt, 0) - isnull(t5.prevAmt, 0) - isnull(t6.prevAmt, 0) -isnull(t7.prevAmt, 0),
	isnull(t4.ytdActualAmt, 0) - isnull(t5.ytdActualAmt, 0) - isnull(t6.ytdActualAmt, 0) - isnull(t7.ytdActualAmt, 0), 
	isnull(t4.ytdLYAmt, 0) - isnull(t5.ytdLYAmt, 0) - isnull(t6.ytdLYAmt, 0) - isnull(t7.ytdLYAmt, 0), 11
	 from @tab t4 join @tab t5 on t4.PrcCode = t5.PrcCode and t4.grMask = 4 and t5.grMask = 5
	join @tab t6 on t5.PrcCode = t6.PrcCode and t6.grMask = 6
	join @tab t7 on t6.PrcCode = t7.PrcCode and t7.grMask = 7
	
	select * from @tab ta
	outer apply (select SUM(gt1.DebLTotal - gt1.CredLTotal) curBud  from 
		OBGS gs 
		left join  OBGT gt on gs.AbsId = gt.Instance
		left join BGT1 gt1 on gt.AbsId = gt1.BudgId and gt1.Line_ID = MONTH(@date) - 1
		left join OACT ct on gt1.AcctCode = ct.AcctCode
		where YEAR(gs.FinancYear) = YEAR(@date) and gs.Locked='N'
		and gs.OcrCode = ta.PrcCode  collate SQL_Latin1_General_CP850_CI_AS
		and ct.GroupMask = ta.grMask) BT
	outer apply(
		select SUM(gt1.DebLTotal - gt1.CredLTotal) ytdLYBud  from 
		OBGS gs 
		left join  OBGT gt on gs.AbsId = gt.Instance
		left join BGT1 gt1 on gt.AbsId = gt1.BudgId and gt1.Line_ID <= MONTH(@dateLY) - 1
		left join OACT ct on gt1.AcctCode = ct.AcctCode
		where YEAR(gs.FinancYear) = YEAR(@dateLY) and gs.Locked='N'
		and gs.OcrCode = ta.PrcCode  collate SQL_Latin1_General_CP850_CI_AS
		and ct.GroupMask = ta.grMask
	
	)	B3
end 

--OPERATING COSTs
--  rpt_AI_FinancialReport '3/3/2013'